﻿using System;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;

namespace test
{

    public class SqlProcess
    {
        private static MySqlConnection conn;
        private static MySqlDataReader reader;
        private String connStr = String.Format("server={0}; user id={1}; password={2}; database={3}; pooling=false",
                        "164.125.37.189", "root", "1234", "constream");


        public OperationRule getOperationRule(String opName)
        {
            OperationRule or = new OperationRule();
            if (conn != null)
                conn.Close();


            conn = new MySqlConnection(connStr);
            conn.Open();

            MySqlCommand cmd = new MySqlCommand("select * from operator_ref where OP_NAME='" + opName + "'", conn);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                or.opName = reader.GetString(0);
                or.opType = reader.GetString(1);
                or.idType = reader.GetString(2);
                or.nThreshold = reader.GetInt16(3);
                or.thresholdName1 = reader.GetString(4);
                or.thresholdName2 = reader.GetString(5);
            }

            if (reader != null) reader.Close();

            return or;
        }

        public bool listingSensorOrDeviceID(String tbName, Object SensorDeviceList)
        {
            DropDownList DropDownSensDevList = (DropDownList)SensorDeviceList;

            if (conn != null)
                conn.Close();
            conn = new MySqlConnection(connStr);
            conn.Open();

            MySqlCommand cmd = new MySqlCommand("select * from " + tbName, conn);
            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                String item = reader.GetString(0);

                DropDownSensDevList.Items.Add(new ListItem(item));
            }

            if (reader != null) reader.Close();

            return true;
        }

        public void addVocabulary(Vocalbulary voca)
        {
            if (conn != null)
                conn.Close();
            conn = new MySqlConnection(connStr);
            conn.Open();
            
            String insertStr = String.Format("INSERT INTO VOCABULARY VALUES('{0}','{1}','{2}','{3}','{4}',{5},NULL )",
                               voca.vocaName , voca.opName, voca.sensDevID, voca.param1 , voca.param2, voca.freq);

            MySqlCommand cmd = new MySqlCommand(insertStr, conn);
            cmd.ExecuteNonQuery();

        }

        public void editVocabulary(Vocalbulary voca, int seq)
        {
            if (conn != null)
                conn.Close();
            conn = new MySqlConnection(connStr);
            conn.Open();

            String updateStr = String.Format("UPDATE VOCABULARY SET VOCA_NAME='{0}', OP_NAME='{1}',SENS_DEV_ID='{2}',THRESHOLD_NAME1='{3}',THRESHOLD_NAME2='{4}',FREQUENCY={5} WHERE SEQ = {6}",
                               voca.vocaName, voca.opName, voca.sensDevID, voca.param1, voca.param2, voca.freq , seq.ToString());

            MySqlCommand cmd = new MySqlCommand(updateStr, conn);
            cmd.ExecuteNonQuery();
        }

        public void deleteVocabulary(int seq)
        {
            if (conn != null)
                conn.Close();
            conn = new MySqlConnection(connStr);
            conn.Open();

            String deleteStr = String.Format("DELETE FROM VOCABULARY WHERE SEQ = {0}",
                               seq.ToString());

            MySqlCommand cmd = new MySqlCommand(deleteStr, conn);
            cmd.ExecuteNonQuery();
        }


    }
}
